use std::fs;

use rusqlite::Connection;

use super::{history::History, migration::Migration};
extern crate dirs;

pub(crate) trait Database {
    fn save(&self, history: &History);
    fn remove(&self, id: &str);
    fn query(&self, filter: &str) -> Vec<History>;
    fn query_all(&self) -> Vec<History>;
}

pub(crate) enum DbType {
    Sqlite,
}

impl DbType {
    pub fn get(&self) -> Box<dyn Database> {
        match self {
            Self::Sqlite => Box::new(Sqlite::new()),
        }
    }
}

pub(crate) struct Sqlite {
    conn: Connection,
}

impl Sqlite {
    pub fn new() -> Self {
        let db_dir = dirs::data_dir()
            .expect("获取数据目录失败")
            .join("passkey");
        if !db_dir.exists() {
            fs::create_dir_all(&db_dir).expect("创建数据目录失败！");
        }
        
        let db_file = db_dir.join("history.db");
        let connection = Connection::open(&db_file).unwrap();
        Self::setup_db(&connection);
        Sqlite { conn: connection }
    }
    
    fn setup_db(conn: &Connection) {
        let migration = Migration::new();
        let scripts = migration.get_scripts();
        for script in scripts {
            conn.execute(&script, ()).unwrap();
        }
    }
}

impl Database for Sqlite {
    fn save(&self, history: &History) {
        let sql = "INSERT INTO history (id, app,pattern,password,remark,create_time) VALUES (?1,?2,?3,?4,?5,?6);"; 
        self.conn.execute(sql, (&history.id, &history.app, &history.pattern, 
                &history.password, &history.remark, &history.create_time)).expect("保存数据失败！");
    }

    fn remove(&self, id: &str) {
        let del_sql = "delete from history where id = ?1;";
        self.conn.execute(del_sql, (&id,)).unwrap();
    }

    fn query(&self, filter: &str) -> Vec<History> {
        let sql = "select h.id, h.app, h.pattern, h.password, h.remark, h.create_time from history h 
            where h.app like ?1 or h.remark like ?2 order by h.create_time desc;";
        let mut smt = self.conn.prepare(sql).unwrap();
        let filter = format!("%{}%", filter);
        let row_iter = smt.query_map((&filter, &filter), |row| {
            let his = History {
                id: row.get(0).unwrap(),
                app: row.get(1).unwrap(),
                pattern: row.get(2).unwrap(),
                password: row.get(3).unwrap(),
                remark: row.get(4).unwrap(),
                create_time: row.get(5).unwrap(),
            };
            Ok(his)
        }).unwrap();

        let mut result = Vec::new();
        for his in row_iter {
            result.push(his.unwrap());
        }

        result
    }

    fn query_all(&self) -> Vec<History> {
        let sql = "select h.id, h.app, h.pattern, h.password, h.remark, h.create_time from history h order by h.create_time desc;";
        let mut smt = self.conn.prepare(sql).unwrap();
        let row_iter = smt.query_map([], |row| {
            let his = History {
                id: row.get(0).unwrap(),
                app: row.get(1).unwrap(),
                pattern: row.get(2).unwrap(),
                password: row.get(3).unwrap(),
                remark: row.get(4).unwrap(),
                create_time: row.get(5).unwrap(),
            };
            Ok(his)
        }).unwrap();

        let mut result = Vec::new();
        for his in row_iter {
            result.push(his.unwrap());
        }

        result
    }
}
